﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CompetitionDAO
/// </summary>
public class CompetitionDAO : DBAccess, ICompetition
{
    #region SQLCommand
    private string SQL_SELECT_ID = "select @@IDENTITY from Competitions";
    private string SQL_SELECT_ALL = "select * from Competitions";
    private string SQL_SELECT_BY_ID = "select * from Competitions where CompetitionID = @competitionId";
    private string SQL_SELECT_BY_NAME = "select * from Competitions where CompetitionName like (@competitionName)";
    private string SQL_INSERT_COMPETITION = "insert into Competitions(CompetitionName, StartDate, EndDate, Conditions, Details, Url) values(@competitionName, @startDate, @endDate, @conditions, @details, @url)";
    private string SQL_UPDATE_COMPETITION = "update Competitions set CompetitionName = @competitionName, StartDate = @startDate, EndDate = @endDate, Conditions = @conditions, Details = @details, Url = @url where CompetitionID = @competitionId";
    private string SQL_DELETE_COMPETITION = "delete from Competitions where CompetitionID = @ID";
    private string SQL_SELECT_ONGOING = "select * from Competitions where StartDate < Getdate() and EndDate > GetDate()";
    private string SQL_SELECT_UPCOMING = "select * from Competitions where StartDate > Getdate()";
    private string SQL_SELECT_PREVIOUS = "select * from Competitions where EndDate < Getdate()";
    private string SQL_GET_NEAREST_PREVIOUS_COMPETITION = "select TOP(1) CompetitionID from Competitions where EndDate < Getdate() order by(EndDate) DESC";

    string message;
   
    #endregion

    public CompetitionDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    #region ICompetition Members

    /// <summary>
    /// Insert Competition into database
    /// </summary>
    /// <param name="com"></param>
    /// <returns>int</returns>
    public int InsertCompetition(CompetitionInfo com)
    {
        int effectedRow = 0;
        try
        {            
            SqlCommand cmd = new SqlCommand(SQL_INSERT_COMPETITION, conn);
            cmd.Parameters.AddWithValue("@competitionName", com.CompetitionName);
            cmd.Parameters.AddWithValue("@startDate", com.StartDate);
            cmd.Parameters.AddWithValue("@endDate", com.EndDate);
            cmd.Parameters.AddWithValue("@conditions", com.Conditions);
            cmd.Parameters.AddWithValue("@details", com.Details);           
            cmd.Parameters.AddWithValue("@url", com.Url);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                cmd.CommandText = SQL_SELECT_ID;
                com.CompetitionID = int.Parse(cmd.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Update competition
    /// </summary>
    /// <param name="com"></param>
    /// <returns>int</returns>
    public int UpdateCompetition(CompetitionInfo com)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_COMPETITION, conn);
            cmd.Parameters.AddWithValue("@competitionId", com.CompetitionID);
            cmd.Parameters.AddWithValue("@competitionName", com.CompetitionName);
            cmd.Parameters.AddWithValue("@startDate", com.StartDate);
            cmd.Parameters.AddWithValue("@endDate", com.EndDate);
            cmd.Parameters.AddWithValue("@conditions", com.Conditions);
            cmd.Parameters.AddWithValue("@details", com.Details);
            cmd.Parameters.AddWithValue("@url", com.Url);

            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
              
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Delete competition
    /// </summary>
    /// <param name="competitionId"></param>
    /// <returns>int</returns>
    public int DeleteCompetition(int competitionId)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_COMPETITION, conn);
            cmd.Parameters.AddWithValue("@ID", competitionId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }


    /// <summary>
    /// Get all competitions
    /// </summary>
    /// <returns>IList</returns>
    public IList<CompetitionInfo> GetAll()
    {
        IList<CompetitionInfo> comList = new List<CompetitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CompetitionInfo com = new CompetitionInfo();
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);

                    comList.Add(com);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return comList;
    }


    /// <summary>
    /// Get competition by ID
    /// </summary>
    /// <param name="id"></param>
    /// <returns>CompetitionInfo</returns>
    public CompetitionInfo getCompetitionID(int id)
    {
        CompetitionInfo com = new CompetitionInfo();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_ID, conn);
            cmd.Parameters.AddWithValue("@competitionId", id);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);
                }
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return com;
    }

    /// <summary>
    /// Select all upcoming competitions
    /// </summary>
    /// <returns>IList</returns>
    public IList<CompetitionInfo> SelectUpcoming()
    {
        IList<CompetitionInfo> comList = new List<CompetitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_UPCOMING, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CompetitionInfo com = new CompetitionInfo();
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);

                    comList.Add(com);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return comList;
    }

    /// <summary>
    /// Select all ongoing competitions
    /// </summary>
    /// <returns>IList</returns>
    public IList<CompetitionInfo> SelectOngoing()
    {
        IList<CompetitionInfo> comList = new List<CompetitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ONGOING, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CompetitionInfo com = new CompetitionInfo();
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);

                    comList.Add(com);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return comList;
    }

    /// <summary>
    /// Get competition by name
    /// </summary>
    /// <param name="name"></param>
    /// <returns>IList</returns>
    public IList<CompetitionInfo> GetCompetitionName(string name)
    {
        IList<CompetitionInfo> comList = new List<CompetitionInfo>();       
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_NAME, conn);
            cmd.Parameters.AddWithValue("@competitionName", name);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CompetitionInfo com = new CompetitionInfo();
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);

                    comList.Add(com);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return comList;
    }


    /// <summary>
    /// Get Previous Competitions
    /// </summary>
    /// <returns>ILlist</returns>
    public IList<CompetitionInfo> SelectPrevious()
    {
        IList<CompetitionInfo> comList = new List<CompetitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_PREVIOUS, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CompetitionInfo com = new CompetitionInfo();
                    com.CompetitionID = reader.GetInt32(0);
                    com.CompetitionName = reader.GetString(1);
                    com.StartDate = reader.GetDateTime(2);
                    com.EndDate = reader.GetDateTime(3);
                    com.Conditions = reader.GetString(4);
                    com.Details = reader.GetString(5);
                    com.Url = reader.GetString(6);

                    comList.Add(com);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return comList;
    }

    /// <summary>
    /// Get nearest Previous Competition
    /// </summary>
    /// <returns>int</returns>
    public int Get_Nearest_PreviousCompetition()
    {
        int competitionID = -1;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GET_NEAREST_PREVIOUS_COMPETITION, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    competitionID = reader.GetInt32(0);
                    break;
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return competitionID;
    }

    #endregion

}